﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
using Microsoft.Office.Interop.Excel;

using Fingu.XJ.XML;
using Fingu.XJ.SQL;
using Fingu.XJ.Excel;
using System.Runtime.InteropServices;
using System.IO;

namespace Fingu.XJ.TRM045.Export
{
    public class clsExport : IDataConnect, IDataQuery, IXML, IExcel
    {
        #region instance obj

        SqlConnection con = new SqlConnection();
        System.Data.DataTable _dataTable = null;
        XmlDocument xmlDoc = new XmlDocument();
        ArrayList _xmlBags = new ArrayList();

        Microsoft.Office.Interop.Excel.Application app;
        Microsoft.Office.Interop.Excel.Workbooks wbs;
        Microsoft.Office.Interop.Excel.Workbook wb;
        Microsoft.Office.Interop.Excel.Worksheet ws;
        Microsoft.Office.Interop.Excel._Worksheet _ws;
        Microsoft.Office.Interop.Excel.Sheets _sheets;

        #endregion

        #region defind variable

        string _connectionString = "";
        string _queryConditons = "";

        string _x1 = "";
        string _y1 = "";
        string _x2 = "";
        string _y2 = "";

        string[] _productID;
        string[] _employer;
        string[] _testDateTime;
        string[] _inspectionState;
        string _productCode;
        string _tesetDateTime2;
        string _processName;
        public void InitProductID(int index)
        {
            try
            {
                this._productID = new string[index];
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public void InitEmployer(int index)
        {
            try
            {
                this._employer = new string[index];
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public void InitTestDateTime(int index)
        {
            try
            {
                this._testDateTime = new string[index];
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public void InitInspectionState(int index)
        {
            try
            {
                this._inspectionState = new string[index];
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public void SetPorductID(string arg, int index)
        {
            try
            {
                this._productID[index] = arg;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
            }
            catch (System.ArgumentOutOfRangeException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public string[] GetProductID()
        {
            return this._productID;
        }
        public void SetEmployer(string arg, int index)
        {
            try
            {
                this._employer[index] = arg;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
            }
            catch (System.ArgumentOutOfRangeException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public string[] GetEmployer()
        {
            return this._employer;
        }
        public void SetTestDateTime(string arg, int index)
        {
            try
            {
                this._testDateTime[index] = arg;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
            }
            catch (System.ArgumentOutOfRangeException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public string[] GetTestDateTime()
        {
            return this._testDateTime;
        }
        public void SetInspectionState(string arg, int index)
        {
            try
            {
                this._inspectionState[index] = arg;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
            }
            catch (System.ArgumentOutOfRangeException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public string[] GetInspectionState()
        {
            return this._inspectionState;
        }
        public void SetProcductCode(string arg)
        {
            try
            {
                this._productCode = arg;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public string GetProductCode()
        {
            return this._productCode;
        }
        public void SetTestDateTime2(DateTime arg)
        {
            try
            {
                this._tesetDateTime2 = arg.Year + "_" + arg.Month + "_" + arg.Day + "_" + arg.Hour + "_" + arg.Minute + "_" + arg.Second;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public string GetTestDateTime2()
        {
            return this._tesetDateTime2;
        }
        public void SetProcessName(string arg)
        {
            this._processName = arg;
        }
        public string GetProcessName()
        {
            return this._processName;
        }


        #endregion

        #region IDataQuery Members

        public bool CreateQueryConditons(string arg)
        {
            try
            {
                string[] _arrQueryFields = arg.Split(',');
                string _productCode = "";
                string _processName = "";
                string _productID = "";
                string _testResult = "";
                string _startDateTime = "";
                string _stopDateTime = "";
                string _testDateTime = "";
                string _inspectionState = "";
                string _sign = " AND ";
                string _finallyString = "";

                _productCode = "ProductCode ='" + GetProductCode() + "'";

                _processName = "Process ='" + GetProcessName() + "'";

                if (_arrQueryFields[0] != "")
                {
                    _productID = "ProductID ='" + _arrQueryFields[0] + "'";
                }
                else
                {
                    _productID = "ProductID !=''";
                }

                if (_arrQueryFields[1] != "")
                {
                    if (_arrQueryFields[1] == "合格")
                    {
                        _testResult = "Result ='0'";
                    }
                    else
                    {
                        _testResult = "Result ='1'";
                    }
                }
                else
                {
                    _testResult = "Result !=''";
                }

                _startDateTime = "'" + _arrQueryFields[2] + "'";
                _stopDateTime = "'" + _arrQueryFields[3] + "'";
                _testDateTime = "BeginTime BETWEEN " + _startDateTime + " AND " + _stopDateTime;

                if (_arrQueryFields[4] != "")
                {
                    _inspectionState = "InspectionState ='" + _arrQueryFields[4] + "'";
                }
                else
                {
                    _inspectionState = "InspectionState !=''";
                }

                _finallyString = "SELECT [ID]" +
                      ",[ProductID]" +
                      ",[Process]" +
                      ",[ProductCode]" +
                      ",[EmployeeID]" +
                      ",[Result]" +
                      ",[Data]" +
                      ",[BeginTime]" +
                      ",[InspectionState]" +
                  "FROM [mcsdbtrial].[dbo].[TB_ProcedureHistory]" +
                  "WHERE "
                  + _productCode + _sign
                  + _processName + _sign
                  + _testResult + _sign
                  + _inspectionState + _sign
                  + _testDateTime;

                _queryConditons = _finallyString;

                return true;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);

                return false;
            }
        }

        public bool Query()
        {
            try
            {
                SqlCommand _cmd = new SqlCommand();
                SqlDataAdapter _sda = new SqlDataAdapter();
                DataSet _ds = new DataSet();
                System.Data.DataTable _dt = new System.Data.DataTable();

                _sda.SelectCommand = new SqlCommand(this._queryConditons, con);

                _sda.Fill(_ds, "Table0");

                _dt = _ds.Tables["Table0"];

                if (_dt.Rows.Count != 0)
                {
                    SetDataTable(_dt);

                    return true;
                }
                else
                {
                    MessageBox.Show("你输入的查询条件返回0条记录！", "条件输入错误",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);

                    return false;
                }
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);

                return false;
            }
            catch (System.NullReferenceException ex)
            {
                MessageBox.Show(ex.Message);

                return false;
            }

        }

        public void SetDataTable(System.Data.DataTable dt)
        {
            this._dataTable = dt;
        }

        public System.Data.DataTable GetDataTable()
        {
            return this._dataTable;
        }

        public void ClearBags()
        {
            _xmlBags.Clear();
        }

        #endregion

        #region IDataConnect Members

        public bool InitConnection(string serverAddress, string DataBaseName, string UserName, string PassWords)
        {
            try
            {
                _connectionString = "server=" + serverAddress + ";"
                                    + "uid=" + UserName + ";"
                                    + "pwd=" + PassWords + ";"
                                    + "database=" + DataBaseName + ";";

                return true;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);

                return false;
            }
        }

        public bool Connection()
        {
            try
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
                else if (con.State == ConnectionState.Closed)
                {
                    con.ConnectionString = this._connectionString;
                    con.Open();
                }

                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);

                return false;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        public bool DisConnection()
        {
            try
            {
                con.Close();

                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);

                return false;
            }

        }

        #endregion

        #region IXML Members

        public bool LoadXML(string arg)
        {
            try
            {
                xmlDoc.LoadXml(arg);
                return true;
            }
            catch (System.NullReferenceException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public bool AnalyzeXML()
        {
            try
            {
                int _iCount = 0;

                StringBuilder arrTempContainer = new StringBuilder();

                string[] arrAnalyzeDatas = null;

                string removeLastsign = "";

                XmlNode xn = xmlDoc.SelectSingleNode("NewDataSet");

                XmlNodeList xnl = xn.ChildNodes;

                foreach (XmlNode xnf in xnl)
                {
                    XmlElement xe = (XmlElement)xnf;

                    if (xnf.Name == "Data")
                    {
                        XmlNodeList xnf1 = xe.ChildNodes;

                        foreach (XmlNode xn2 in xnf1)
                        {
                            //记得将累加对象_iCount = -1初始化。
                            if (xn2.Name == "TestResult")
                            {
                                _iCount++;

                                arrTempContainer.Append(xn2.InnerText + "|");
                            }
                        }
                    }
                }

                arrAnalyzeDatas = new string[_iCount];

                removeLastsign = arrTempContainer.ToString().Substring(0, arrTempContainer.Length - 1);

                arrAnalyzeDatas = removeLastsign.Split('|');

                SetXmlDataBags(arrAnalyzeDatas);

                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public ArrayList GetXmlDataBags()
        {
            return _xmlBags;
        }

        public void SetXmlDataBags(string[] arg)
        {
            try
            {
                this._xmlBags.Add(arg);
            }
            catch (System.NullReferenceException ex)
            {
                MessageBox.Show(ex.Message);
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #endregion

        #region IExcel Members

        public void SetSheetPage(int arg)
        {
            _sheets = wb.Worksheets;

            _ws = (_Worksheet)_sheets.get_Item(arg);
        }

        public Microsoft.Office.Interop.Excel._Worksheet GetSheetPage()
        {
            return this._ws;
        }

        public bool Create()
        {
            try
            {
                System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                app = new Microsoft.Office.Interop.Excel.Application();
                wbs = app.Workbooks;
                wb = wbs.Add(true);
                app.Visible = true;
                return true;
            }
            catch (System.NullReferenceException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public bool Open(string filePath, bool appVisible, string pw)
        {
            try
            {
                System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                if (System.IO.File.Exists(filePath) == true)
                {
                    app = new Microsoft.Office.Interop.Excel.Application();

                    app.Visible = appVisible;

                    wbs = app.Workbooks;

                    wb = wbs.Open(filePath, 0, false, 5, pw, "", true,
                        Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t",
                        false, false, 0, true, Type.Missing, Type.Missing);

                    return true;
                }
                else
                {
                    MessageBox.Show(filePath + ": 不存在！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    return false;
                }

            }
            catch (ArgumentNullException msg)
            {
                MessageBox.Show(msg.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

                return false;
            }
            catch (NullReferenceException msg)
            {
                MessageBox.Show(msg.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

                return false;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

                return false;
            }
        }

        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out   int ID);
        public new bool Close()
        {
            try
            {
                IntPtr t = new IntPtr(app.Hwnd);

                int k = 0;

                GetWindowThreadProcessId(t, out k);

                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);

                p.Kill();

                return true;
            }
            catch (System.NullReferenceException ex)
            {
                MessageBox.Show(ex.Message);

                return false;
            }
        }

        public bool WriteIn(string ProductID, string[] Datas, string Employer, string TestDateTime, string InspectionState)
        {
            try
            {
                #region 向指定范围内写入数据。

                string[] arrayContainer = new string[Datas.Length + 4];

                arrayContainer[0] = ProductID;

                for (int i = 1; i <= arrayContainer.GetUpperBound(0) - 3; i++)
                {
                    arrayContainer[i] = Datas[i - 1];
                }

                arrayContainer[arrayContainer.GetUpperBound(0) - 2] = Employer;

                arrayContainer[arrayContainer.GetUpperBound(0) - 1] = TestDateTime;

                arrayContainer[arrayContainer.GetUpperBound(0)] = InspectionState;

                for (int i = 0; i <= arrayContainer.GetUpperBound(0); i++)
                {
                    GetSheetPage().Cells[Convert.ToInt64(GetStartCell()),
                                        Convert.ToInt64(GetStartColumn()) + i] = arrayContainer[i];
                }
                #endregion

                return true;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);

                return false;
            }
            catch (System.ArgumentOutOfRangeException ex)
            {
                MessageBox.Show(ex.Message);

                return false;
            }
        }

        public bool WriteIn(string[] ProductID, ArrayList Datas, string[] Employer, string[] TestDateTime, string[] InspectionState)
        {
            try
            {
                #region 向指定范围内写入数据。

                int jrow = -1;
                int arrayIndex = -1;

                foreach (string[] arg in Datas)
                {
                    jrow++;

                    arrayIndex++;

                    string[] arrayContainer = new string[arg.Length + 4];

                    arrayContainer[0] = ProductID[arrayIndex];

                    for (int i = 1; i <= arrayContainer.GetUpperBound(0) - 3; i++)
                    {
                        arrayContainer[i] = arg[i - 1];
                    }

                    arrayContainer[arrayContainer.GetUpperBound(0) - 2] = Employer[arrayIndex];

                    arrayContainer[arrayContainer.GetUpperBound(0) - 1] = TestDateTime[arrayIndex];

                    arrayContainer[arrayContainer.GetUpperBound(0)] = InspectionState[arrayIndex];

                    for (int icolumn = 0; icolumn <= arrayContainer.GetUpperBound(0); icolumn++)
                    {
                        GetSheetPage().Cells[Convert.ToInt64(GetStartCell()) + jrow,
                                            Convert.ToInt64(GetStartColumn()) + icolumn] = arrayContainer[icolumn];
                    }
                }


                #endregion

                return true;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);

                return false;
            }
            catch (System.ArgumentOutOfRangeException ex)
            {
                MessageBox.Show(ex.Message);

                return false;
            }
        }

        public bool ReadOut(ref string[,] Datas)
        {
            try
            {
                #region 得到数据范围

                //string[] _sr = SheetRange.Split(',');
                //int _x1 = Convert.ToInt16(_sr[0]);
                //int _y1 = Convert.ToInt16(_sr[1]);
                //int _x2 = Convert.ToInt16(_sr[2]);
                //int _y2 = Convert.ToInt16(_sr[3]);

                #endregion

                #region 指定活动的页面

                //Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets;

                //_Worksheet worksheet = (_Worksheet)sheets.get_Item(SheetObj);

                //if (worksheet == null)
                //{
                //    MessageBox.Show("ERROR: worksheet == null");

                //    return false;
                //}

                //SetSheetPage(SheetObj.ToString());                

                #endregion

                #region 读取范围内的数据。

                //Datas = new string[Math.Abs(_x1 - _x2) + 1, Math.Abs(_y1 - _y2) + 1];

                //for (int i = 0; i <= Datas.GetUpperBound(0); i++)
                //{
                //    for (int j = 0; j <= Datas.GetUpperBound(1); j++)
                //    {
                //        Microsoft.Office.Interop.Excel.Range range =
                //            worksheet.get_Range(worksheet.Cells[i + _x1, j + _y1],
                //                                worksheet.Cells[i + _x1, j + _y1]);

                //        Datas[i, j] = range.Text.ToString();
                //    }
                //}

                Datas = new string[Math.Abs(Convert.ToInt64(GetStartCell()) - Convert.ToInt64(GetStopCell())) + 1,
                    Math.Abs(Convert.ToInt64(GetStartColumn()) - Convert.ToInt64(GetStopColumn())) + 1];

                for (int i = 0; i <= Datas.GetUpperBound(0); i++)
                {
                    for (int j = 0; j <= Datas.GetUpperBound(1); j++)
                    {
                        Microsoft.Office.Interop.Excel.Range range =
                            GetSheetPage().get_Range(GetSheetPage().Cells[i + Convert.ToInt64(GetStartCell()), j + Convert.ToInt64(GetStopCell())],
                                                     GetSheetPage().Cells[i + Convert.ToInt64(GetStartCell()), j + Convert.ToInt64(GetStopCell())]);

                        Datas[i, j] = range.Text.ToString();
                    }
                }

                #endregion

                return true;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
            catch (System.ArgumentOutOfRangeException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public bool Save(string filePath)
        {
            try
            {
                wb.Save();

                return true;
            }

            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.ToString());

                return false;
            }
            catch (System.NullReferenceException ex)
            {
                MessageBox.Show(ex.ToString());

                return false;
            }
        }

        public bool SavaAs(string filePath)
        {
            try
            {
                wb.SaveAs(filePath, Type.Missing, Type.Missing,
                          Type.Missing, Type.Missing, Type.Missing,
                          Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                          Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                return true;
            }
            catch (System.ArgumentNullException ex)
            {
                MessageBox.Show(ex.ToString());

                return false;
            }
            catch (System.NullReferenceException ex)
            {
                MessageBox.Show(ex.ToString());

                return false;
            }
        }

        public void SetStartCell(string arg)
        {
            this._x1 = arg;
        }

        public string GetStartCell()
        {
            return this._x1;
        }

        public void SetStartColumn(string arg)
        {
            this._y1 = arg;
        }

        public string GetStartColumn()
        {
            return this._y1;
        }

        public void SetStopCell(string arg)
        {
            this._x2 = arg;
        }

        public string GetStopCell()
        {
            return this._x2;
        }

        public void SetStopColumn(string arg)
        {
            this._y2 = arg;
        }

        public string GetStopColumn()
        {
            return this._y2;
        }

        #endregion
    }
}
